package com.lobaby.tools.common.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author Administrator
 *         Created by Administrator on 2020/12/28 0028.
 */
public class ExcelUtils {
    private final static Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    /**
     * 带时间表头的初始化
     *
     * @param sheet
     * @param tablename
     * @param style
     * @param timestr
     * @return
     */
    public static int initTime(HSSFSheet sheet, String tablename, HSSFCellStyle style, String timestr) {
        init(sheet, tablename, style);
        HSSFRow row_time = sheet.createRow(1);
        HSSFCell cell_time = row_time.createCell(0);

        String exportTime = "报表导出时间：" + DateUtils.formatDate(new Date(), DateUtils.DATE_FORMAT_YYYY_MM_DD);
        String quertTime = timestr == null ? "" : "，报表查询时间：" + timestr;
        cell_time.setCellValue(exportTime + quertTime);
        cell_time.setCellStyle(style);
        return 2;
    }

    /**
     * 带查询条件表头的初始化
     *
     * @param sheet
     * @param tablename
     * @param style
     * @param filter
     * @return
     */
    public static int initExport(HSSFSheet sheet, String tablename, HSSFCellStyle style, String filter) {
        init(sheet, tablename, style);
        HSSFRow row_time = sheet.createRow(1);
        HSSFCell cell_time = row_time.createCell(0);

        String exportTime = "导出时间：" + DateUtils.formatDate(new Date(), DateUtils.DATE_FORMAT_YYYY_MM_DD);
        String quertTime = filter == null ? "" : "，查询条件：" + filter;
        cell_time.setCellValue(exportTime + quertTime);
        cell_time.setCellStyle(style);
        return 2;
    }

    /**
     * 不带时间表头的初始化
     * @param sheet
     * @param tablename
     * @param style
     * @return
     */
    public static int init(HSSFSheet sheet, String tablename, HSSFCellStyle style) {
        /**
         * 初始化第一行的表头
         */
        HSSFRow row_name = sheet.createRow(0);
        HSSFCell cell = row_name.createCell(0);
        cell.setCellValue(tablename);
        cell.setCellStyle(style);

        return 1;
    }

    /**
     * 合并单元格
     *
     * @param sheet
     * @param address
     */
    public static void setMergecols(HSSFSheet sheet, CellRangeAddress... address) {
        Arrays.asList(address).stream().forEach(sheet::addMergedRegion);
    }

    /**
     * 获得一个工作表
     *
     * @param file
     * @return
     */
    public static Workbook getWorkbook(File file) throws Exception{
        Workbook result = null;
        String type = StringUtil.getFileType(file);
        if (type.equals(".xls")) {
            result = new HSSFWorkbook(new FileInputStream(file));
        } else {
            result = new XSSFWorkbook(new FileInputStream(file));
        }

        return result;
    }

    /**
     * 获取工作表
     * @param is
     * @param fileType
     * @return
     */
    public static Workbook getWorkbook(InputStream is, String fileType) {
        Workbook workbook = null;

        try {
            if (fileType.equals(XLS)) {
                workbook = new HSSFWorkbook(is);
            } else if (fileType.equals(XLSX)) {
                workbook = new XSSFWorkbook(is);
            } else {
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return workbook;
    }

    /**
     * 获得一个工作簿
     *
     * @param book
     * @param sheetname
     * @return
     */
    public static HSSFSheet getSheet(HSSFWorkbook book, String sheetname) {
        HSSFSheet sheet = book.createSheet(sheetname);
        return sheet;
    }

    public static Sheet getSheet(Workbook book, String sheetname) {
        Sheet sheet = book.getSheet(sheetname);
        return sheet;
    }

    /**
     * 获得一个单元格的样式
     *
     * @param book
     * @param style
     * @return
     */
    public static HSSFCellStyle getStyle(HSSFWorkbook book, String... style) {
        HSSFCellStyle cellstyle = book.createCellStyle();
        for (int i = 0; i < style.length; i++) {
            switch (style[i]) {
                case "border":
                    cellstyle.setBorderBottom(BorderStyle.THIN);
                    cellstyle.setBorderLeft(BorderStyle.THIN);
                    cellstyle.setBorderTop(BorderStyle.THIN);
                    cellstyle.setBorderRight(BorderStyle.THIN);
                    break;
                case "center":
                    cellstyle.setAlignment(HorizontalAlignment.CENTER);
                    cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    break;
                case "colorError":
                    HSSFFont font = book.createFont();
                    font.setColor(HSSFColor.RED.index);
                    cellstyle.setFont(font);
                    cellstyle.setAlignment(HorizontalAlignment.CENTER);
                    cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    break;
                default:
                    break;
            }
        }

        return cellstyle;
    }

    public static CellStyle getStyle(Workbook book, String... style) {
        CellStyle cellstyle = book.createCellStyle();
        for (int i = 0; i < style.length; i++) {
            switch (style[i]) {
                case "border":
                    cellstyle.setBorderBottom(BorderStyle.THIN);
                    cellstyle.setBorderLeft(BorderStyle.THIN);
                    cellstyle.setBorderTop(BorderStyle.THIN);
                    cellstyle.setBorderRight(BorderStyle.THIN);
                    break;
                case "center":
                    cellstyle.setAlignment(HorizontalAlignment.CENTER);
                    cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    break;
                case "colorError":
                    Font font = book.createFont();
                    font.setColor(HSSFColor.RED.index);
                    cellstyle.setFont(font);
                    cellstyle.setAlignment(HorizontalAlignment.LEFT);
                    cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    break;
                case "autoFiexd":
                    cellstyle.setWrapText(true);
                    cellstyle.setAlignment(HorizontalAlignment.LEFT);
                    cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    break;
                default:
                    break;
            }
        }

        return cellstyle;
    }

    /**
     * 获取字体
     * 字体大小默认11
     * 字体名默认宋体
     *
     * @param book
     * @return
     */
    public static HSSFFont getFont(HSSFWorkbook book) {
        HSSFFont font = book.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);

        return font;
    }

    /**
     * 获取字体
     * 字体大小默认11
     *
     * @param book
     * @param fontname：字体名
     * @return
     */
    public static HSSFFont getFont(HSSFWorkbook book, String fontname) {
        HSSFFont font = book.createFont();
        font.setFontName(fontname);
        font.setFontHeightInPoints((short) 11);

        return font;
    }

    /**
     * 获取字体
     *
     * @param book
     * @param fontname：字体名
     * @param fontsize：字体大小
     * @return
     */
    public static HSSFFont getFont(HSSFWorkbook book, String fontname, int fontsize) {
        HSSFFont font = book.createFont();
        font.setFontName(fontname);
        font.setFontHeightInPoints((short) fontsize);

        return font;
    }

    public static void writeExcel(OutputStream os, List<? extends Object> list, String tableName, String[] table_title, String[] properties, Map<String, String> params) {
        HSSFWorkbook book = new HSSFWorkbook();

        HSSFCellStyle style = ExcelUtils.getStyle(book, "border", "center");
        style.setFont(ExcelUtils.getFont(book));

        HSSFSheet sheet = ExcelUtils.getSheet(book, "导出报表");
        ExcelUtils.setMergecols(sheet, new CellRangeAddress(0, 0, 0, table_title.length - 1), new CellRangeAddress(1, 1, 0, table_title.length - 1));

        /**
         * 初始化表头
         */
        int rowNums = ExcelUtils.initTime(sheet, tableName, style, params.get("querytime"));
        HSSFRow row_title = sheet.createRow(rowNums);

        for (int i = 0; i < table_title.length; i++) {
            sheet.setColumnWidth(i, table_title[i].getBytes().length * 2 * 256);
            sheet.setDefaultColumnStyle(i, style);
            HSSFCell cell = row_title.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(table_title[i]);
        }

        for (int i = 0; i < list.size(); i++) {
            HSSFRow row = sheet.createRow(i + 3);
            Object obj = list.get(i);
            for (int j = 0; j < properties.length; j++) {
                Class<? extends Object> clazz = obj.getClass();
                try {
                    if (clazz.getName().indexOf("HashMap") != -1) {
                        @SuppressWarnings("unchecked")
                        Map<String, Object> dataMap = (Map<String, Object>) obj;
                        row.createCell(j).setCellValue(dataMap.get(properties[j])==null?"":dataMap.get(properties[j]).toString());
                    } else {
                        Field field = clazz.getDeclaredField(properties[j]);
                        field.setAccessible(true);
                        String value = field.get(obj) == null ? "" : field.get(obj).toString();
                        row.createCell(j).setCellValue(value);
                    }
                } catch (Exception e) {
                    LOGGER.error("",e);
                }
            }
        }
        try {
            book.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            LOGGER.error("",e);
        }
    }

    public static void writeExcel(OutputStream os, List<? extends Object> list, String tableName, String[] table_title, String[] properties, boolean isnullContent) {
        HSSFWorkbook book = new HSSFWorkbook();

        HSSFCellStyle style = ExcelUtils.getStyle(book, "border", "center");
        style.setFont(ExcelUtils.getFont(book));

        HSSFSheet sheet = ExcelUtils.getSheet(book, "导出报表");
        ExcelUtils.setMergecols(sheet, new CellRangeAddress(0, 0, 0, table_title.length - 1), new CellRangeAddress(1, 1, 0, table_title.length - 1));

        /**
         * 初始化表头
         */
        int rowNums = ExcelUtils.initTime(sheet, tableName, style, null);
        HSSFRow row_title = sheet.createRow(rowNums);

        for (int i = 0; i < table_title.length; i++) {
            sheet.setColumnWidth(i, table_title[i].getBytes().length * 2 * 256);
            sheet.setDefaultColumnStyle(i, style);
            HSSFCell cell = row_title.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(table_title[i]);
        }

        for (int i = 0; i < list.size(); i++) {
            HSSFRow row = sheet.createRow(i + 3);
            Object obj = list.get(i);
            for (int j = 0; j < properties.length; j++) {
                Class<? extends Object> clazz = obj.getClass();
                try {
                    if (clazz.getName().indexOf("HashMap") != -1) {
                        @SuppressWarnings("unchecked")
                        Map<String, Object> dataMap = (Map<String, Object>) obj;
                        if (isnullContent) {
                            Object value = dataMap.get(properties[j]);
                            String values = value == null ? "-" : value.toString();
                            row.createCell(j).setCellValue(values);
                        } else {
                            row.createCell(j).setCellValue(dataMap.get(properties[j]).toString());
                        }
                    } else {
                        Field field = clazz.getDeclaredField(properties[j]);
                        field.setAccessible(true);
                        String value = field.get(obj) == null ? "" : field.get(obj).toString();
                        row.createCell(j).setCellValue(value);
                    }
                } catch (Exception e) {
                    LOGGER.error("",e);
                }
            }
        }
        try {
            book.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            LOGGER.error("",e);
        }
    }

    public static void writeExcel(OutputStream os, List<? extends Object> list, String tableName, String[] table_title, String[] properties, boolean isnullContent, Map<String, String> params) {
        HSSFWorkbook book = new HSSFWorkbook();

        HSSFCellStyle style = ExcelUtils.getStyle(book, "border", "center");
        style.setFont(ExcelUtils.getFont(book));

        HSSFSheet sheet = ExcelUtils.getSheet(book, "导出报表");
        ExcelUtils.setMergecols(sheet, new CellRangeAddress(0, 0, 0, table_title.length - 1), new CellRangeAddress(1, 1, 0, table_title.length - 1));

        /**
         * 初始化表头
         */
        int rowNums = ExcelUtils.initTime(sheet, tableName, style, params.get("querytime"));
        HSSFRow row_title = sheet.createRow(rowNums);

        for (int i = 0; i < table_title.length; i++) {
            sheet.setColumnWidth(i, table_title[i].getBytes().length * 2 * 256);
            sheet.setDefaultColumnStyle(i, style);
            HSSFCell cell = row_title.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(table_title[i]);
        }

        for (int i = 0; i < list.size(); i++) {
            HSSFRow row = sheet.createRow(i + 3);
            Object obj = list.get(i);
            for (int j = 0; j < properties.length; j++) {
                Class<? extends Object> clazz = obj.getClass();
                try {
                    if (clazz.getName().indexOf("HashMap") != -1) {
                        @SuppressWarnings("unchecked")
                        Map<String, Object> dataMap = (Map<String, Object>) obj;
                        if (isnullContent) {
                            Object value = dataMap.get(properties[j]);
                            String values = value == null ? "-" : value.toString();
                            row.createCell(j).setCellValue(values);
                        } else {
                            row.createCell(j).setCellValue(dataMap.get(properties[j]).toString());
                        }
                    } else {
                        Field field = clazz.getDeclaredField(properties[j]);
                        field.setAccessible(true);
                        String value = field.get(obj) == null ? "" : field.get(obj).toString();
                        row.createCell(j).setCellValue(value);
                    }
                } catch (Exception e) {
                    LOGGER.error("",e);
                }
            }
        }
        try {
            book.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            LOGGER.error("",e);
        }
    }

    /**
     * 新建一个工作表
     * @param workbook
     * @return
     */
    public static Sheet buildDataSheet(Workbook workbook, String[] titles) {
        Sheet sheet = workbook.createSheet();
        // 设置列头宽度
        for (int i = 0; i < titles.length; i++) {
            sheet.setColumnWidth(i, 4000);
        }
        // 设置默认行高
        sheet.setDefaultRowHeight((short) 400);
        // 构建头单元格样式
        CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());
        // 写入第一行各列的数据
        Row head = sheet.createRow(0);
        for (int i = 0; i < titles.length; i++) {
            Cell cell = head.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(cellStyle);
        }
        return sheet;
    }

    /**
     * 设置样式
     * @param workbook
     * @return
     */
    public static CellStyle buildHeadCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //对齐方式设置
        style.setAlignment(HorizontalAlignment.CENTER);
        //边框颜色和宽度设置
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框
        //设置背景颜色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //粗体字设置
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    public static void initRow(Row row, Row param) {
        for (int i = 0, n = param.getLastCellNum(); i < n; i++) {
            row.createCell(i).setCellValue(param.getCell(i).getStringCellValue());
        }
    }

    public static String getCellValue(Cell cell, Workbook workbook) {
        try {
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

            if (cell != null) {
                CellType type = cell.getCellTypeEnum();
                switch (type) {
                    case BOOLEAN:
                        return String.valueOf(cell.getBooleanCellValue());
                    case ERROR:
                        return null;
                    case STRING:
                        return cell.getStringCellValue();
                    case _NONE:
                        return null;
                    case NUMERIC:
                        return new DecimalFormat("#").format(cell.getNumericCellValue());
                    case FORMULA:
                        CellValue evaluate = evaluator.evaluate(cell);
                        return evaluate.getCellTypeEnum() == CellType.NUMERIC ? String.valueOf(evaluate.getNumberValue()) : evaluate.getStringValue();
//						return String.valueOf(cell.getNumericCellValue());
                    default:
                        return "";
                }
            } else {
                return "";
            }
        } catch (Exception e) {
            LOGGER.error("", e);
            return "";
        }
    }
}
